package saii.integracion.wrapper.fd3;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import saii.business.modelo.Asignatura;
import saii.business.modelo.AsignaturaPlanEstudios;
import saii.business.modelo.PlanEstudios;
import saii.integracion.utilidades.Archivo_Propiedades;
import saii.integracion.wrapper.relacional.FuenteDatosRelacional;
import saii.integracion.wrapper.relacional.IFilaRelacional;
import saii.integracion.wrapper.relacional.IFuenteDatosRelacional;
import saii.integracion.wrapper.relacional.ITablaRelacional;

public class WrapperFD3 implements IWrapperFD3 {

	Properties propiedades = new Archivo_Propiedades().getProperties();
	
	private String HOST = "";
	private int PUERTO = Integer.parseInt(propiedades.getProperty("PUERTO_FD3"));
	private String USUARIO = propiedades.getProperty("USUARIO_FD3");
	private String PASSWORD = propiedades.getProperty("PASSWORD_FD3");
	private String SCHEMA = propiedades.getProperty("SCHEMA_FD3");

	private ConnectionPoolFD3 pool;

	public WrapperFD3() throws SQLException {

		// obtenemos una conexion.

		this.pool = ConnectionPoolFD3.getInstance();
	}

	@Override
	public String getHost() {
		return this.HOST;
	}

	@Override
	public int getPuertoConexion() {

		return this.PUERTO;
	}

	@Override
	public String getUser() {
		return this.USUARIO;
	}

	@Override
	public String getPassword() {
		return this.PASSWORD;
	}

	@Override
	public ITablaRelacional getTablaRelacional(String nombreTabla) {
		return null;
	}

	@Override
	public IFilaRelacional getFilaRelacional(int numeroFila, String nombreTabla) {
		return null;
	}

	@Override
	public ArrayList<IFilaRelacional> getConjuntoFilaRelacional(
			int limiteInferior, int limiteSuperior, String nombreTabla) {
		return null;
	}

	@Override
	public IFuenteDatosRelacional getFuenteDatos() throws SQLException {

		// creamos un objeto de tipo Fuente

		ArrayList<String> tablasFuente = new ArrayList<String>();

		Statement statement = this.pool.getConnection().createStatement();

		ResultSet tablasResulset = statement.executeQuery("SHOW TABLES FROM "
				+ this.SCHEMA + ";");

		while (tablasResulset.next()) {
			tablasFuente.add(tablasResulset.getString(1));

		}
		return new FuenteDatosRelacional(tablasFuente);

	}


	@Override
	public IAsignaturaFD3 getAsignaturaFD3ById(String idAsig) {
		return getAsignaturaByFiltro(idAsig, "codAsig");
	}
	
	@Override
	public IAsignaturaFD3 getAsignaturaByFiltro(String filtro, String columna) {

		try {
			AsignaturaFD3 asignatura = new AsignaturaFD3();

			Statement statement = this.pool.getConnection().createStatement();
			String query = "SELECT * FROM " + this.SCHEMA
					+ ".ASIGNATURA WHERE " + columna + "=\"" + filtro + "\";";
			ResultSet resultSet = statement.executeQuery(query);

			if (resultSet.next()) {
				asignatura.setCodAsig(resultSet.getString("codAsig"));
				asignatura.setNombre(resultSet.getString("nombre"));
				asignatura.setCreditos(resultSet.getInt("creditos"));
			}

			return asignatura;
		} catch (SQLException e) {
			System.out.println(e);
		}

		return null;
	}
	

	@Override
	public IPlanFD3 getPlanByFiltro(String filtro, String columna) {
		try {
			PlanFD3 plan = new PlanFD3();

			Statement statement = this.pool.getConnection().createStatement();
			String query = "SELECT * FROM " + this.SCHEMA + ".PLAN WHERE "
					+ columna + "=\"" + filtro + "\";";
			ResultSet resultSet = statement.executeQuery(query);

			if (resultSet.next()) {
				plan.setCodPlan(resultSet.getString("codPlan"));
				plan.setFacultad(resultSet.getString("facultad"));
				plan.setFechaImplantacion(resultSet
						.getDate("fechaImplantacion"));
				plan.setNombre(resultSet.getString("nombre"));
				plan.setNumCreditos(resultSet.getInt("numCreditos"));
			}

			return plan;
		} catch (SQLException e) {
			System.out.println(e);
		}

		return null;
	}

	
	@SuppressWarnings("deprecation")
	@Override
	public IAsignaturaPlanFD3 getAsignaturaPlanByFiltro(String filtro,
			String columna) {
		try {
			AsignaturaPlanFD3 asigPlan = new AsignaturaPlanFD3();

			Statement statement = this.pool.getConnection().createStatement();
			String query = "SELECT * FROM " + this.SCHEMA
					+ ".ASIGNATURA_PLAN WHERE " + columna + "=\"" + filtro
					+ "\";";
			ResultSet resultSet = statement.executeQuery(query);

			if (resultSet.next()) {
				asigPlan.setCodAsig(resultSet.getString("codAsig"));
				asigPlan.setCodPlan(resultSet.getString("codPlan"));
				asigPlan.setCurso(resultSet.getDate("curso").getYear());
				asigPlan.setTipoAsignatura(resultSet
						.getString("tipoAsignatura"));
			}

			return asigPlan;
		} catch (SQLException e) {
			System.out.println(e);
		}

		return null;
	}

	@Override
	public ArrayList<IPlanFD3> getPlanesFD3() {
		try {
			ArrayList<IPlanFD3> listaAsigPlan = new ArrayList<IPlanFD3>();
			
			Statement statement = this.pool.getConnection().createStatement();
			String query = "SELECT * FROM " + this.SCHEMA + ".PLAN;";
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				PlanFD3 plan = new PlanFD3();
				plan.setCodPlan(resultSet.getString("codPlan"));
				plan.setFacultad(resultSet.getString("facultad"));
				plan.setFechaImplantacion(resultSet
						.getDate("fechaImplantacion"));
				plan.setNombre(resultSet.getString("nombre"));
				plan.setNumCreditos(resultSet.getInt("numCreditos"));
				listaAsigPlan.add(plan);
			}

			return listaAsigPlan;
		} catch (SQLException e) {
			System.out.println(e);
		}
		return null;
	}

	@SuppressWarnings("deprecation")
	@Override
	public ArrayList<IAsignaturaPlanFD3> getAsignaturasPlanesFD3() {
		try {
			ArrayList<IAsignaturaPlanFD3> listaAsigPlan = new ArrayList<IAsignaturaPlanFD3>();

			Statement statement = this.pool.getConnection().createStatement();
			String query = "SELECT * FROM " + this.SCHEMA + ".ASIGNATURA_PLAN;";
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				AsignaturaPlanFD3 asigPlan = new AsignaturaPlanFD3();
				asigPlan.setCodAsig(resultSet.getString("codAsig"));
				asigPlan.setCodPlan(resultSet.getString("codPlan"));
				asigPlan.setCurso(resultSet.getDate("curso").getYear());
				asigPlan.setTipoAsignatura(resultSet
						.getString("tipoAsignatura"));
				listaAsigPlan.add(asigPlan);
			}

			return listaAsigPlan;
		} catch (SQLException e) {
			System.out.println(e);
		}

		return null;
	}

	@Override
	public ArrayList<IAsignaturaFD3> getAsignaturasFD3() {
		try {
			ArrayList<IAsignaturaFD3> listaAsignaturas = new ArrayList<IAsignaturaFD3>();

			Statement statement = this.pool.getConnection().createStatement();
			String query = "SELECT * FROM " + this.SCHEMA
					+ ".ASIGNATURA;";
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				AsignaturaFD3 asignatura = new AsignaturaFD3();
				asignatura.setCodAsig(resultSet.getString("codAsig"));
				asignatura.setNombre(resultSet.getString("nombre"));
				asignatura.setCreditos(resultSet.getInt("creditos"));
				listaAsignaturas.add(asignatura);
			}

			return listaAsignaturas;
		} catch (SQLException e) {
			System.out.println(e);
		}

		return null;
	}

	public ConcurrentHashMap<String, Asignatura> getAsignaturasEIHM() {
		
		Asignatura asig = null;
		
		Statement statement;
		
		ConcurrentHashMap<String, Asignatura> hma = new ConcurrentHashMap<String,Asignatura>();
		
		try {
			statement = this.pool.getConnection().createStatement();

			ResultSet asignaturas = statement.executeQuery("SELECT * FROM "
					+ this.SCHEMA + ".ASIGNATURA;");

			while (asignaturas.next()) {

				asig= new Asignatura(asignaturas.getString("codAsig"), asignaturas.getString("nombre"), "ND", asignaturas.getString("creditos") ,"ND");
				
				hma.put(asignaturas.getString("codAsig"), asig);


			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return hma;

	}
	
	public ConcurrentHashMap<String, AsignaturaPlanEstudios> getAsignaturasPlanEIHM() {
		
		AsignaturaPlanEstudios asigPlan = null;
		
		Statement statement;
		
		ConcurrentHashMap<String, AsignaturaPlanEstudios> hma = new ConcurrentHashMap<String,AsignaturaPlanEstudios>();
		
		try {
			statement = this.pool.getConnection().createStatement();

			ResultSet asignaturasPlan = statement.executeQuery("SELECT * FROM "
					+ this.SCHEMA + ".ASIGNATURA_PLAN;");

			while (asignaturasPlan.next()) {

				asigPlan= new AsignaturaPlanEstudios(asignaturasPlan.getString("codAsig"), asignaturasPlan.getString("codPlan"),asignaturasPlan.getString("curso") ,asignaturasPlan.getString("tipoAsignatura"));
				
				hma.put(asignaturasPlan.getString("codAsig"), asigPlan);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return hma;

	}

	public ConcurrentHashMap<String, PlanEstudios> getPlanesEIHM() {
		
		PlanEstudios plan = null;
		
		Statement statement;
		
		ConcurrentHashMap<String, PlanEstudios> hma = new ConcurrentHashMap<String,PlanEstudios>();
		
		try {
			statement = this.pool.getConnection().createStatement();

			ResultSet planes = statement.executeQuery("SELECT * FROM "
					+ this.SCHEMA + ".PLAN;");

			while (planes.next()) {

				plan= new PlanEstudios(planes.getString("codPlan"),planes.getString("facultad") ,planes.getString("numCreditos"));
				
				hma.put(planes.getString("codPlan"), plan);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return hma;

	}

}
